package com.cup.bddm.impala.dao;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.cup.bddm.impala.Constant;
import com.cup.bddm.impala.ImpalaJdbc;
import com.cup.bddm.impala.po.BranchDailyTrans;
import com.cup.bddm.impala.po.BranchGeneDailyTrans;
import com.cup.bddm.impala.po.BranchStreetStatInfo;
import com.cup.bddm.impala.po.ServiceMchntStatInfo;
import com.cup.bddm.util.DateUtil;

@Repository("branchDao")
public class BranchDao {

    static Logger logger = LoggerFactory.getLogger(BranchDao.class);

    @Autowired
    private ImpalaJdbc impalaJdbc;

    // @Override
    // public BranchGeneDailyTrans getBranchGeneralDailyTrans(String branchCd,
    // String date) {
    // String sql = "SELECT * from " + Constant.TBL_BRANCH_GENERAL_DAY_TRANS
    // + " WHERE row_key=?";
    //
    //
    // logger.info("exacute sql:" + sql + " . paremeter: cup_branch_id_cd=" +
    // branchCd + ". pdate=" + date);
    // BranchGeneDailyTrans dailyTrans = new BranchGeneDailyTrans();
    //
    // try {
    // Map<String, Object> map = jdbcTemplate.query(sql, date + "|" + branchCd);
    // if (map != null) {
    // dailyTrans.setCup_branch_ins_id_cd(map.get("cup_branch_ins_id_cd").toString());
    // dailyTrans.setCup_branch_nm(map.get("cup_branch_nm").toString());
    // dailyTrans.setSq_count((BigDecimal)map.get("sq_count"));
    // dailyTrans.setJq_count((BigDecimal)map.get("jq_count"));
    // dailyTrans.setMchnt_count((BigDecimal)map.get("mchnt_count"));
    // dailyTrans.setAll_trans_count((BigDecimal)map.get("all_trans_count"));
    // dailyTrans.setAll_trans_sum((BigDecimal)map.get("all_trans_sum"));
    // dailyTrans.setNfc_trans_count((BigDecimal)map.get("nfc_trans_count"));
    // dailyTrans.setNfc_trans_sum((BigDecimal)map.get("nfc_trans_sum"));
    // dailyTrans.setQrc_trans_count((BigDecimal)map.get("qrc_trans_count"));
    // dailyTrans.setQrc_trans_sum((BigDecimal)map.get("qrc_trans_sum"));
    // dailyTrans.setOffline_trans_count((BigDecimal)map.get("offline_trans_count"));
    // dailyTrans.setOffline_trans_sum((BigDecimal)map.get("offline_trans_sum"));
    // dailyTrans.setPdate(map.get("pdate").toString());
    //
    // } else {
    //
    // dailyTrans.setCup_branch_ins_id_cd(branchCd);
    // dailyTrans.setSq_count(new BigDecimal("0"));
    // dailyTrans.setJq_count(new BigDecimal("0"));
    // dailyTrans.setMchnt_count(new BigDecimal("0"));
    // dailyTrans.setAll_trans_count(new BigDecimal("0"));
    // dailyTrans.setAll_trans_sum(new BigDecimal("0"));
    // dailyTrans.setNfc_trans_count(new BigDecimal("0"));
    // dailyTrans.setNfc_trans_sum(new BigDecimal("0"));
    // dailyTrans.setQrc_trans_count(new BigDecimal("0"));
    // dailyTrans.setQrc_trans_sum(new BigDecimal("0"));
    // dailyTrans.setOffline_trans_count(new BigDecimal("0"));
    // dailyTrans.setOffline_trans_sum(new BigDecimal("0"));
    // dailyTrans.setPdate(date);
    // }
    // } catch (SQLException e) {
    // //
    // logger.error("error in query branch general day trans ", e);
    // throw new RuntimeException("error in query branch general day trans
    // info");
    // }
    // return dailyTrans;
    // }

    /** 获取分公司当日交易情况 */
    public List<BranchGeneDailyTrans> getCityGeneTrans(String branchCd, String date) {
        String row_key_start = branchCd + "|" + date + "|";
        String row_key_end = branchCd + "|" + date + "}";
        String sql = "SELECT * from " + Constant.TBL_BRANCH_CITY_GENERAL_DAY_TRANS + " WHERE row_key>=? and row_key<=?";

        logger.info("exacute sql:" + sql + " . paremeter: cup_branch_id_cd=" + branchCd + ". pdate=" + date);
        List<BranchGeneDailyTrans> branchGeneralTransList = new ArrayList<BranchGeneDailyTrans>();

        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, row_key_start, row_key_end);
            for (Map<String, Object> map : list) {
                BranchGeneDailyTrans generalTrans = new BranchGeneDailyTrans();
                generalTrans.setCup_branch_ins_id_cd(map.get("cup_branch_ins_id_cd").toString());
                generalTrans.setCup_branch_nm(map.get("cup_branch_nm").toString());
                generalTrans.setCity(map.get("city").toString());
                generalTrans.setSq_count((BigDecimal) map.get("sq_count"));
                generalTrans.setJq_count((BigDecimal) map.get("jq_count"));
                generalTrans.setMchnt_count((BigDecimal) map.get("mchnt_count"));
                generalTrans.setAll_trans_count((BigDecimal) map.get("all_trans_count"));
                generalTrans.setAll_trans_sum((BigDecimal) map.get("all_trans_sum"));
                generalTrans.setNfc_trans_count((BigDecimal) map.get("nfc_trans_count"));
                generalTrans.setNfc_trans_sum((BigDecimal) map.get("nfc_trans_sum"));
                generalTrans.setQrc_trans_count((BigDecimal) map.get("qrc_trans_count"));
                generalTrans.setQrc_trans_sum((BigDecimal) map.get("qrc_trans_sum"));
                generalTrans.setOffline_trans_count((BigDecimal) map.get("offline_trans_count"));
                generalTrans.setOffline_trans_sum((BigDecimal) map.get("offline_trans_sum"));
                generalTrans.setPdate(map.get("pdate").toString());
                branchGeneralTransList.add(generalTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query branch general trans info", e);
            throw new RuntimeException("error in query branch general trans info");
        }
        return branchGeneralTransList;
    }

    /** 获取当日交易交易类型分布 */
    public BranchDailyTrans getBranchDailyTrans(String branchCd, String date, String type) {
        String sql = "";
        if (type.equals("all")) {
            sql = "select * from " + Constant.TBL_BRANCH_ALL_DAY_TRANS + " where row_key=?";
        } else if (type.equals("nfc")) {
            sql = "select * from " + Constant.TBL_BRANCH_NFC_DAY_TRANS + " where row_key=?";
        } else if (type.equals("qrc")) {
            sql = "select * from " + Constant.TBL_BRANCH_QRC_DAY_TRANS + " where row_key=?";
        } else if (type.equals("offline")) {
            sql = "select * from " + Constant.TBL_BRANCH_OFFLINE_DAY_TRANS + " where row_key=?";
        } else {
            return null;
        }

        logger.info("exacute sql:" + sql + " . paremeter: cup_branch_id_cd=" + branchCd + ". pdate=" + date);
        BranchDailyTrans dailyTrans = new BranchDailyTrans();

        try {
            Map<String, Object> map = impalaJdbc.queryForMap(sql, branchCd + "|" + date);
            if (map != null) {

                dailyTrans.setPdate(map.get("pdate").toString());
                dailyTrans.setTrans_count((BigDecimal) map.get("trans_count"));
                dailyTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
                dailyTrans.setAvg_trans_at((BigDecimal) map.get("avg_trans_at"));
                dailyTrans.setCard_count((BigDecimal) map.get("card_count"));
                dailyTrans.setLe_10_count((BigDecimal) map.get("le_10_count"));
                dailyTrans.setBw_10_50_count((BigDecimal) map.get("bw_10_50_count"));
                dailyTrans.setBw_50_200_count((BigDecimal) map.get("bw_50_200_count"));
                dailyTrans.setBw_200_1000_count((BigDecimal) map.get("bw_200_1000_count"));
                dailyTrans.setGt_1000_count((BigDecimal) map.get("gt_1000_count"));
            } else {

                dailyTrans.setPdate(date);
                dailyTrans.setTrans_count(new BigDecimal("0"));
                dailyTrans.setTrans_sum(new BigDecimal("0"));
                dailyTrans.setAvg_trans_at(new BigDecimal("0"));
                dailyTrans.setCard_count(new BigDecimal("0"));
                dailyTrans.setLe_10_count(new BigDecimal("0"));
                dailyTrans.setBw_10_50_count(new BigDecimal("0"));
                dailyTrans.setBw_50_200_count(new BigDecimal("0"));
                dailyTrans.setBw_200_1000_count(new BigDecimal("0"));
                dailyTrans.setGt_1000_count(new BigDecimal("0"));
            }
        } catch (SQLException e) {
            logger.error("error in query branch day trans ", e);
            throw new RuntimeException("error in query branch  day trans info");
        }
        return dailyTrans;
    }

    /** 获取当日交易商户类型分布 */
    public Map<String, BranchDailyTrans> getBranchTransByTransType(String branchCd, String date) {

        Map<String, BranchDailyTrans> transTypeDis = new HashMap<String, BranchDailyTrans>();

        BranchDailyTrans nfcTrans = getBranchDailyTrans(branchCd, date, "nfc");
        BranchDailyTrans qrcTrans = getBranchDailyTrans(branchCd, date, "qrc");
        BranchDailyTrans offlineTrans = getBranchDailyTrans(branchCd, date, "offline");

        transTypeDis.put("nfc", nfcTrans);
        transTypeDis.put("qrc", qrcTrans);
        transTypeDis.put("offline", offlineTrans);

        return transTypeDis;
    }

    /** 获取分公司商圈拓展情况 */
    public Map<String, BranchDailyTrans> getBranchTransByMchntType(String branchCd, String date) {
        String nextDay = DateUtil.getNextDay(date);
        String sql = "select mchnt_tp, trans_count from " + Constant.TBL_BRANCH_MCHNT_TP_DAY_TRANS + " where row_key>=? and row_key<?";

        logger.info("exacute sql:" + sql + " . paremeter: cup_branch_id_cd=" + branchCd + ". pdate=" + date);
        Map<String, BranchDailyTrans> mchntTpTrans = new HashMap<String, BranchDailyTrans>();

        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, branchCd + "|" + date, branchCd + "|" + nextDay);
            for (Map<String, Object> map : list) {
                BranchDailyTrans dayTrans = new BranchDailyTrans();

                dayTrans.setTrans_count((BigDecimal) map.get("trans_count"));
                mchntTpTrans.put(map.get("mchnt_tp").toString(), dayTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query branch trans dist by mchnt tp ", e);
            throw new RuntimeException("error in query branch day trans dist by mchnt tp");
        }
        return mchntTpTrans;
    }

    /** 获取分公司服务商拓展商圈交易情况 */
    public List<ServiceMchntStatInfo> getBranchServiceMchntStatInfo(String branchCd, String date) {
        String braCode = branchCd.substring(4, 8);
        String sql = "SELECT s.service_mchnt_cd,s.service_mchnt_nm, s.biz_street_cd,s.biz_street_nm ,t.trans_count from " + Constant.TBL_QRC_STREET_INFO + " s JOIN " + Constant.TBL_STREET_ALL_DAY_TRANS + " t on s.biz_street_cd=t.biz_street_cd" + " AND s.row_key>=?" + " AND s.row_key<=?" + " AND t.row_key>=?" + " AND t.row_key<=?" + " AND t.pdate=?";

        logger.info("exacute sql:" + sql + " . paremeter: braCode=" + braCode + ". pdate=" + date);
        List<ServiceMchntStatInfo> serviceMchntStatList = new ArrayList<ServiceMchntStatInfo>();

        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, braCode + "A", braCode + "Z", braCode + "A", braCode + "Z", date);
            for (Map<String, Object> map : list) {
                ServiceMchntStatInfo serviceMchntStat = new ServiceMchntStatInfo();
                serviceMchntStat.setService_mchnt_cd(map.get("service_mchnt_cd").toString());
                serviceMchntStat.setService_mchnt_nm(map.get("service_mchnt_nm").toString());
                serviceMchntStat.setStreet_cd(map.get("biz_street_cd").toString());
                serviceMchntStat.setStreet_nm(map.get("biz_street_nm").toString());
                serviceMchntStat.setTrans_count((BigDecimal) map.get("trans_count"));
                serviceMchntStatList.add(serviceMchntStat);
            }

        } catch (SQLException e) {
            logger.error("error in query branch service mchnt stat info ", e);
            throw new RuntimeException("error in query branch service mchnt stat info ");
        }
        return serviceMchntStatList;
    }

    /** 获取最新数据的日期 */
    public List<BranchStreetStatInfo> getBranchStreetStatInfo(String branchCd, String date) {
        String nextDay = DateUtil.getNextDay(date);
        String sql = "SELECT * from " + Constant.TBL_BRANCH_BIZ_STREET_DAY_TRANS + " WHERE row_key>=? and row_key<?";

        logger.info("exacute sql:" + sql + " . paremeter: cup_branch_id_cd=" + branchCd + ". pdate=" + date);

        List<BranchStreetStatInfo> branchStreetTransList = new ArrayList<BranchStreetStatInfo>();

        try {
            List<Map<String, Object>> list = impalaJdbc.queryForList(sql, branchCd + "|" + date, branchCd + "|" + nextDay);
            for (Map<String, Object> map : list) {
                BranchStreetStatInfo branchStreetTrans = new BranchStreetStatInfo();
                branchStreetTrans.setCup_branch_ins_id_cd(map.get("cup_branch_ins_id_cd").toString());
                branchStreetTrans.setCup_branch_nm(map.get("cup_branch_nm").toString());
                branchStreetTrans.setStreet_cd(map.get("biz_street_cd").toString());
                branchStreetTrans.setStreet_nm(map.get("biz_street_nm").toString());
                branchStreetTrans.setService_mchnt_count((BigDecimal) map.get("service_mchnt_count"));
                branchStreetTrans.setTrans_count((BigDecimal) map.get("trans_count"));
                branchStreetTrans.setTrans_sum((BigDecimal) map.get("trans_sum"));
                branchStreetTrans.setActive_mchnt_count((BigDecimal) map.get("active_mchnt_count"));
                branchStreetTrans.setMchnt_count((BigDecimal) map.get("mchnt_count"));

                branchStreetTransList.add(branchStreetTrans);
            }

        } catch (SQLException e) {
            logger.error("error in query branch street stat info ", e);
            throw new RuntimeException("error in query branch street stat info ");
        }

        return branchStreetTransList;
    }

    /** 功能：根据分公司和日期获取交易信息 */
    public String getRecentDate() {
        String sql = "select max(pdate) as rec_date from " + Constant.TBL_BRANCH_GENERAL_DAY_TRANS;
        String date = "";
        try {
            Map<String, Object> result = impalaJdbc.queryForMap(sql);
            if (result != null) {
                date = (String) result.get("rec_date");
            }

        } catch (SQLException e) {
            logger.error("error in query recent date", e);
            throw new RuntimeException("error in query recent date");

        }
        return date;
    }

}
